unit PartTimePaymentForm;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.ComCtrls, Vcl.StdCtrls, DB,
  DateUtils, DBModule, Vcl.Grids, Vcl.DBGrids, UserUtils, IB;

type
  TfmPartTimePayment = class(TForm)
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    txtYear: TEdit;
    txtMonth: TEdit;
    UpDown1: TUpDown;
    UpDown2: TUpDown;
    chkFiltered: TCheckBox;
    DBGrid1: TDBGrid;
    btnCommit: TButton;
    Button2: TButton;
    procedure FormCreate(Sender: TObject);
    procedure DBGrid1TitleClick(Column: TColumn);
    procedure chkFilteredClick(Sender: TObject);
    procedure txtYearChange(Sender: TObject);
    procedure txtMonthChange(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Button2Click(Sender: TObject);
    procedure btnCommitClick(Sender: TObject);
  private
    { Private declarations }
    procedure DoFilter(UseFilter: boolean);
  public
    { Public declarations }
  end;

var
  fmPartTimePayment: TfmPartTimePayment;

implementation

{$R *.dfm}

procedure TfmPartTimePayment.btnCommitClick(Sender: TObject);
var
  ID: integer;
begin
  with DataModule1.qrySQL do
  begin
    try
      try
        DBGrid1.DataSource.DataSet.First;
        while not DBGrid1.DataSource.DataSet.Eof do
        begin
          SQL.Clear;
          Params.Clear;
          SQL.Add('select * from EXPENSES where NAME=:Name and PERIOD_YEAR=:Year and PERIOD_MONTH=:Month and REFERENCE=:Reference');
          Params.ParamByName('Name').AsString := DBGrid1.DataSource.DataSet.FieldByName('Name').AsString;
          Params.ParamByName('Year').AsInteger := YearOf(DBGrid1.DataSource.DataSet.FieldByName('DATE_OF_SHOOT').AsDateTime);
          Params.ParamByName('Month').AsInteger := MonthOf(DBGrid1.DataSource.DataSet.FieldByName('DATE_OF_SHOOT').AsDateTime);
          Params.ParamByName('Reference').AsString := DBGrid1.DataSource.DataSet.FieldByName('INVOICE_NO').AsString;
          if not Prepared then
            Prepare;
          Open;
//          if not DataModule1.fbExpenses.Active then
//            DataModule1.fbExpenses.Open;
{          if DataModule1.fbExpenses.Locate('NAME;PERIOD_YEAR;PERIOD_MONTH',
                             VarArrayOf([DBGrid1.DataSource.DataSet.FieldByName('Name').AsString,
                                        YearOf(DBGrid1.DataSource.DataSet.FieldByName('DATE_OF_SHOOT').AsDateTime),
                                        MonthOf(DBGrid1.DataSource.DataSet.FieldByName('DATE_OF_SHOOT').AsDateTime)]),
                             []) then }
          if RecordCount = 1 then
          begin
            ID := FieldByName('ID').AsInteger;
            SQL.Clear;
            Params.Clear;
            SQL.Add('update EXPENSES set NAME=:Name, ' +
                    'PERIOD_YEAR=:Year, PERIOD_MONTH=:Month, AMOUNT=:Amount, ' +
                    'REFERENCE=:Reference, UPDATE_DATE=CURRENT_TIMESTAMP where ID=:ID');
            Params.ParamByName('ID').AsInteger := ID;
          end else begin
            SQL.Clear;
            Params.Clear;
            SQL.Add('insert into EXPENSES (CATEGORY_ID, NAME, PERIOD_YEAR, ' +
                    'PERIOD_MONTH, AMOUNT, REFERENCE, BILLABLE, DUE_DATE, ' +
                    'CREATE_DATE, UPDATE_DATE) ' +
                    'values (:CategoryID, :Name, :Year, :Month, :Amount, ' +
                    ':Reference, ''0'', ''1900-01-01'', ' +
                    'CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)');
            Params.ParamByName('CategoryID').AsInteger := 6;
          end;
          Params.ParamByName('Name').AsString := DBGrid1.DataSource.DataSet.FieldByName('NAME').AsString;
          Params.ParamByName('Year').AsInteger := YearOf(DBGrid1.DataSource.DataSet.FieldByName('DATE_OF_SHOOT').AsDateTime);
          Params.ParamByName('Month').AsInteger := MonthOf(DBGrid1.DataSource.DataSet.FieldByName('DATE_OF_SHOOT').AsDateTime);
          Params.ParamByName('Amount').AsFloat := DBGrid1.DataSource.DataSet.FieldByName('AMOUNT').AsFloat;
          Params.ParamByName('Reference').AsString := DBGrid1.DataSource.DataSet.FieldByName('INVOICE_NO').AsString;
          if not Prepared then
            Prepare;
          ExecSQL;
          DBGrid1.DataSource.DataSet.Next;
        end;
        Transaction.Commit;
      except
        on E: EIBInterBaseError do
        begin
          Transaction.Rollback;
          ShowMessage(Format('IBErrorCode=%d, SQLCode=%d, Msg=%s, StackTrace=%s', [E.IBErrorCode, E.SQLCode, E.Message, E.StackTrace]));
        end;
      end;
    finally
      with DataModule1.qryPartTimePayment do
        if not Active then
          Open;
      with DataModule1.fbExpenses do
        if not Active then
          Open
        else
          Refresh;
    end;
  end;
end;

procedure TfmPartTimePayment.Button2Click(Sender: TObject);
begin
  Close;
end;

procedure TfmPartTimePayment.chkFilteredClick(Sender: TObject);
begin
  if chkFiltered.Checked then
  begin
    txtYear.Enabled := True;
    UpDown1.Enabled := True;
    txtMonth.Enabled := True;
    UpDown2.Enabled := True;
    DoFilter(True);
  end else begin
    txtYear.Enabled := False;
    UpDown1.Enabled := False;
    txtMonth.Enabled := False;
    UpDown2.Enabled := False;
    DoFilter(False);
  end;
end;

procedure TfmPartTimePayment.DBGrid1TitleClick(Column: TColumn);
var
  s: string;
begin
  with DataModule1.qryPartTimePayment do
  begin
    s := SQLBeforeClause(SQL.Text, 'Order By');
    Close;
    SQL.Clear;
    SQL.Add(s);
    SQL.Add('Order By ' + Column.FieldName);
    Open;
  end;
end;

procedure TfmPartTimePayment.FormClose(Sender: TObject;
  var Action: TCloseAction);
begin
  FreeAndNil(fmPartTimePayment);
end;

procedure TfmPartTimePayment.FormCreate(Sender: TObject);
begin
  UpDown1.Position := YearOf(Today);
  UpDown2.Position := MonthOf(Today);
end;

procedure TfmPartTimePayment.txtMonthChange(Sender: TObject);
begin
  DoFilter(True);
end;

procedure TfmPartTimePayment.txtYearChange(Sender: TObject);
begin
  DoFilter(True);
end;

procedure TfmPartTimePayment.DoFilter(UseFilter: boolean);
var
  s: string;
begin
  with DataModule1.qryPartTimePayment do
  begin
    s := SQLBeforeClause(SQL.Text, 'Order By');
    s := SQLBeforeClause(s, 'Where');
    Close;
    SQL.Clear;
    Params.Clear;
    SQL.Add(s);
    if UseFilter then
      SQL.Add('Where Extract(Year from DATE_OF_SHOOT)=:Year And Extract(Month from DATE_OF_SHOOT)=:Month');
    SQL.Add('Order By INVOICE_NO');
    if UseFilter then
    begin
      Params.ParamByName('Year').AsString := txtYear.Text;
      Params.ParamByName('Month').AsString := txtMonth.Text;
    end;
    if not Prepared then
      Prepare;
    Open;
  end;
end;

end.
